package com.carpart;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.carpart.bean.CarPart;
import com.carpart.util.Configuration;
import com.carpart.util.JdbcUtil;


public class DataAccess {
	
	public void init(String year, String model, String area, String postcode, String part, String style){
		Connection conn = null;
		try {
			conn = JdbcUtil.getConnection();
			CallableStatement call = conn.prepareCall("{call sp_init_cart_part(?, ?, ?, ?, ?, ?, ?)}");  
			call.setString(1, year);
			call.setString(2, model);
			call.setString(3, area);
			call.setString(4, postcode);
			call.setString(5, part);
			call.setString(6, style);
			call.setString(7, "0");
			call.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeQuietly(conn);
		}
	}
	
	
	public void insert(CarPart carPart){
		Connection conn = null;
		try {
			conn = JdbcUtil.getConnection();
			CallableStatement call = conn.prepareCall("{call sp_insert_car_part(?,?,?,?,?,?,?,?,?,?,?,?,?)}");  
			call.setString(1, carPart.getYear());
			call.setString(2, carPart.getModel());
			call.setString(3, carPart.getPart());
			call.setString(4, carPart.getArea());
			call.setString(5, carPart.getPostcode());
			call.setString(6, carPart.getStyle());
			call.setString(7, carPart.getYearPartModel());
			call.setString(8, carPart.getPrice());
			call.setString(9, carPart.getDistMile());
			call.setString(10, carPart.getDescription());
			call.setString(11, carPart.getDealer());
			call.setString(12, carPart.getGrade());
			call.setString(13, carPart.getStock());
			call.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeQuietly(conn);
		}
	}
	
	public void insert(List<CarPart> carParts){
		if(null != carParts && carParts.size()>0){
			Connection conn = null;
			try {
				conn = JdbcUtil.getConnection();
				for(CarPart carPart : carParts){
					try{
						CallableStatement call = conn.prepareCall("{call sp_insert_car_part(?,?,?,?,?,?,?,?,?,?,?,?,?)}");  
						call.setString(1, carPart.getYear());
						call.setString(2, carPart.getModel());
						call.setString(3, carPart.getPart());
						call.setString(4, carPart.getArea());
						call.setString(5, carPart.getPostcode());
						call.setString(6, carPart.getStyle());
						call.setString(7, carPart.getYearPartModel());
						call.setString(8, carPart.getPrice());
						call.setString(9, carPart.getDistMile());
						call.setString(10, carPart.getDescription());
						call.setString(11, carPart.getDealer());
						call.setString(12, carPart.getGrade());
						call.setString(13, carPart.getStock());
						call.execute();
					}catch (Exception e) {
						e.printStackTrace();
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				JdbcUtil.closeQuietly(conn);
			}
		}
	}
	
	public List<String> queryStyle(String year, String model, String area, String postCode, String part, String style){
		List<String> list = new ArrayList<String>();
		Connection conn = null;
		try {
			conn = JdbcUtil.getConnection();
			CallableStatement call = conn.prepareCall("{call sp_init_cart_part(?, ?, ?, ?, ?, ?, ?)}");  
			call.setString(1, year);
			call.setString(2, model);
			call.setString(3, area);
			call.setString(4, postCode);
			call.setString(5, part);
			call.setString(6, style);
			call.setString(7, "1");
			
			ResultSet rs = call.executeQuery();
			while(rs.next()){
				list.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeQuietly(conn);
		}
		return list;
	}
	
	public List<CarPart> query(String year,String postcode, String model, String area, String part, String style){
		List<CarPart> list = new ArrayList<CarPart>();
		Connection conn = null;
		try {
			conn = JdbcUtil.getConnection();
			CallableStatement call = conn.prepareCall("{call sp_query_car_part(?, ?, ?, ?, ?, ?)}");  
			call.setString(1, year);
			call.setString(2, model);
			call.setString(3, area);
			call.setString(4, postcode);
			call.setString(5, part);
			call.setString(6, style);
			ResultSet rs = call.executeQuery();
			while(rs.next()){
				CarPart carPart = new CarPart();
				carPart.setYear(rs.getString(1));
				carPart.setPart(rs.getString(2));
				carPart.setPrice(rs.getString(3));
				carPart.setDistMile(rs.getString(4));
				carPart.setStyle(rs.getString(5));
				carPart.setDescription(rs.getString(6));
				carPart.setDealer(rs.getString(7));
				list.add(carPart);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeQuietly(conn);
		}
		return list;
	}
	
	public static void main(String[] args) {
		Configuration.load();
		JdbcUtil.init();
		DataAccess db = new DataAccess();
		
//		db.init("2009", "", "", "", "", "");
		
//		CarPart carPart = new CarPart();
//		carPart.setYear("2008");
//		db.insert(carPart);
		
//		List<CarPart> list = db.query("2008", "", "", "", "", "");
//		System.out.println(list.size());
		
		List<String> list = db.queryStyle("2008", "", "", "", "", "");
		System.out.println(list.size());
	}
	
}
